import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;

/**
 * 生成随机数据
 */
public class GenerateData {
    String surname = readFile("D:\\Workspace\\java\\data-ready\\app\\src\\姓300.txt");
    String word = readFile("D:\\Workspace\\java\\data-ready\\app\\src\\字.txt");
    ArrayList<DeptData> deptDataList = readExcel();

    //读取文本文件内容
    public String readFile(String filePath) {
        StringBuffer buffer = new StringBuffer();
        try {
            BufferedReader reader = new BufferedReader(new FileReader(filePath));
            String line;
            while ((line = reader.readLine()) != null) {
                buffer.append(line);
            }
            reader.close();
        } catch (IOException e) {
            System.out.println("读取文件异常>>" + e.getMessage());
        }
        return buffer.toString();
    }

    //指定范围产生一个随机数
    public int getRandomNum(int min, int max) {
        Random random = new Random();
        return random.nextInt(max - min + 1) + min;
    }

    //随机获取一组姓名
    public ArrayList<String> getNames(int num) {
        ArrayList names = new ArrayList<>();
        for (int i = 0; i < num; i++) {
            char[] surnames = surname.toCharArray();
            char[] words = word.toCharArray();
            int surIndex = getRandomNum(0, surnames.length - 1);
            int wordLen = getRandomNum(1, 2);
            int wordFirstIndex = getRandomNum(0, words.length - 1);
            int wordSecondIndex = getRandomNum(0, words.length - 1);
            String name = wordLen == 2 ? String.valueOf(surnames[surIndex]) + String.valueOf(words[wordFirstIndex]) +
                    String.valueOf(words[wordSecondIndex]) : String.valueOf(surnames[surIndex]) + String.valueOf(words[wordFirstIndex]);
            names.add(name);
        }

        return names;
    }

    //读取表格数据
    public ArrayList<DeptData> readExcel() {
        ArrayList<DeptData> dataList = new ArrayList<>();
        String excelFilePath = "D:\\Workspace\\java\\data-ready\\app\\src\\系-专业.xlsx";
        try (FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            for (Row row : sheet) { // 迭代每一行
                if (row.getRowNum() != 0) {
                    DeptData deptData = new DeptData();
                    for (Cell cell : row) { // 迭代每一列
                        switch (cell.getColumnIndex()) {
                            case 0://第0列
                                deptData.setDno(getCellVal(cell));
                                break;
                            case 1://第1列
                                deptData.setDname(getCellVal(cell));
                                break;
                            case 2://第2列
                                deptData.setMno(getCellVal(cell));
                                break;
                            case 3://第3列
                                deptData.setMname(getCellVal(cell));
                                break;
                        }

                    }
                    dataList.add(deptData);
                }
            }
        } catch (IOException e) {
            System.out.println("IO异常>>" + e.getMessage());
            e.printStackTrace();
        } catch (Exception e2) {
            System.out.println("异常>>" + e2.getMessage());
            e2.printStackTrace();
        }

        return dataList;
    }

    public String getCellVal(Cell cell) {
        String val = "";
        // 根据不同数据类型，以字符串形式输出数据
        switch (cell.getCellType()) {
            case STRING:
                val = cell.getStringCellValue();
                break;
            case NUMERIC:
                val = cell.getNumericCellValue() + "";
                break;
            case BOOLEAN:
                val = cell.getBooleanCellValue() + "";
                break;
            case FORMULA:
                val = cell.getCellFormula() + "";
                break;
            default:
                break;
        }
        return val;
    }

    //获取系统时间
    private String getSystemDateTime() {
        // 获取当前的日期时间
        LocalDateTime now = LocalDateTime.now();
        // 定义日期时间格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        // 使用formatter格式化日期时间
        String formattedDateTime = now.format(formatter);
        return formattedDateTime;
    }

    //新增系
    public ArrayList<String> getAddDeptSql() {
        SnowFlake snowFlake = new SnowFlake(2, 3);
        ArrayList sqls = new ArrayList();
        for (DeptData deptData : deptDataList) {
            String sql = "INSERT IGNORE INTO dept (id,dno,dname,dleader,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','" + deptData.getDno() + "','" + deptData.getDname() + "','" + getNames(1).get(0) + "','" + 0 + "','" + 1 + "','" + getSystemDateTime() + "');";
            sqls.add(sql);
        }
        return sqls;
    }

    //新增专业
    public ArrayList<String> getAddMajorSql(ArrayList<Map<String, String>> depts) {
        SnowFlake snowFlake = new SnowFlake(2, 3);
        ArrayList sqls = new ArrayList();
        for (DeptData deptData : deptDataList) {
            for (Map<String, String> dept : depts) {
                if (dept.get("dno").equals(deptData.getDno())) {
                    String sql = "INSERT IGNORE INTO major (id,mno,mname,dept_id,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','" + deptData.getMno() + "','" + deptData.getMname() + "'," + dept.get("id") + ",0,1,'" + getSystemDateTime() + "');";
                    sqls.add(sql);
                }
            }
        }
        return sqls;
    }

    //新增学生
    public ArrayList<HashMap<String, String>> getAddStudentSql(ArrayList<HashMap<String, String>> majors) {
        SnowFlake snowFlake = new SnowFlake(2, 3);
        ArrayList<HashMap<String, String>> datas = new ArrayList();
        for (int i = 490001; i <= 500000; i++) {
            DeptData deptData = deptDataList.get(getRandomNum(0, deptDataList.size() - 1));
            for (HashMap<String, String> major : majors) {
                if (major.get("mno").equals(deptData.getMno())) {
                    int sex = getRandomNum(1, 2);
                    String sno = "S" + String.valueOf(i + 1000000).substring(1);
//                    String sql = "INSERT IGNORE INTO student (id,sno,sname,major_id,sex,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','" + sno + "','" + getNames(1).get(0) + "','" + major.get("id") + "','" + sex + "',0,1,'" + getSystemDateTime() + "');";
                    HashMap map = new HashMap();
                    map.put("id", snowFlake.nextId());
                    map.put("sno", sno);
                    map.put("sname", getNames(1).get(0));
                    map.put("major_id", major.get("id"));
                    map.put("sex", sex);
                    map.put("status", 0);
                    map.put("version", 1);
                    map.put("create_time", getSystemDateTime());
                    datas.add(map);
                }
            }
        }
        return datas;
    }

    //新增课程
    public ArrayList<String> getAddCourseSql() {
        SnowFlake snowFlake = new SnowFlake(2, 3);
        ArrayList sqls = new ArrayList();
        String sql1 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0001','大学英语',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql1);
        String sql2 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0002','高等数学',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql2);
        String sql3 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0003','马克思主义中国化进程',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql3);
        String sql4 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0004','思想道德修养与法律基础',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql4);
        String sql5 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0005','体育',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql5);
        String sql6 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0006','计算机系统概论',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql6);
        String sql7 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0007','电子技术基础',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql7);
        String sql8 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0008','中国近代史',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql8);
        String sql9 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0009','数据库系统概论',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql9);
        String sql10 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0010','大学语文',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql10);
        String sql11 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0011','化学工程与工艺',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql11);
        String sql12 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0012','建筑力学',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql12);
        String sql13 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0013','自动控制原理',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql13);
        String sql14 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0014','机电学',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql14);
        String sql15 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0015','概率论与数理统计',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql15);
        String sql16 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0016','生物化学',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql16);
        String sql17 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0017','药理学',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql17);
        String sql18 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0018','建筑设计基础',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql18);
        String sql19 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0019','作物遗传学',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql19);
        String sql20 = "INSERT IGNORE INTO course (id,cno,cname,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','C0020','园艺植物育种与栽培',0,1,'" + getSystemDateTime() + "');";
        sqls.add(sql20);
        return sqls;
    }

    //新增学生-课程信息
    public ArrayList<HashMap<String, String>> getAddStuCourseSql(ArrayList<HashMap<String, String>> stus, ArrayList<HashMap<String, Long>> courses) {
        SnowFlake snowFlake = new SnowFlake(2, 3);
        ArrayList<HashMap<String, String>> datas = new ArrayList();
        for (int i = 0; i < stus.size() - 1; i++) {
            int cNum = getRandomNum(1, 5);//每个学生上1-5门课程
            for (int j = 0; j < cNum; j++) {
                int cIndex = getRandomNum(0, courses.size() - 1);
                String course_id = courses.get(cIndex).get("id") + "";
                HashMap map = new HashMap();
                map.put("id", snowFlake.nextId());
                map.put("stu_id", stus.get(i).get("id"));
                map.put("course_id", course_id);
                map.put("session", "202401");
                map.put("status", 0);
                map.put("version", 1);
                map.put("create_time", getSystemDateTime());
                datas.add(map);
            }
        }
//        for (HashMap<String, String> stu : stus) {
//            int cNum = getRandomNum(1, 5);//每个学生上1-5门课程
//            for (int i = 0; i < cNum; i++) {
//                int cIndex = getRandomNum(0, courses.size() - 1);
//                String course_id = courses.get(cIndex).get("id") + "";
////                String sql = "INSERT IGNORE INTO stu_course (id,stu_id,course_id,`session`,`status`,version,create_time) VALUES('" + snowFlake.nextId() + "','" + stu.get("id") + "','" + course_id + "','202401',0,1,'" + getSystemDateTime() + "');";
//                HashMap map = new HashMap();
//                map.put("id", snowFlake.nextId());
//                map.put("stu_id", stu.get("id"));
//                map.put("course_id", course_id);
//                map.put("session", "202401");
//                map.put("status", 0);
//                map.put("version", 1);
//                map.put("create_time", getSystemDateTime());
//                datas.add(map);
//            }
//        }
        return datas;
    }

    //操作数据库
    public void MySQLOperate() {
        String url = "jdbc:mysql://localhost:3306/test";//数据库地址
        String name = "root";//用户名
        String password = "123456";//密码

        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建连接
            Connection conn = DriverManager.getConnection(url, name, password);
            //创建操作对象
            Statement statement = conn.createStatement();

            //=======================插入系数据===============================================
//            System.out.println("=======================插入系开始========================");
//            for (String sql : getAddDeptSql()) {
//                statement.executeUpdate(sql);
//                System.out.println(sql);
//            }
//            System.out.println("=======================插入系结束========================");

            //========================插入专业数据=============================================
//            String sqlD = "SELECT  * FROM `dept`";
//            ResultSet resultSetS = statement.executeQuery(sqlD);
//            ArrayList<Map<String, String>> deptIds = new ArrayList<>();
//            while (resultSetS.next()) {
//                HashMap map = new HashMap();
//                map.put("id", resultSetS.getObject("id") + "");
//                map.put("dno", resultSetS.getObject("dno") + "");
//                deptIds.add(map);
//            }
//            System.out.println("=======================插入专业开始========================");
//            for (String sql : getAddMajorSql(deptIds)) {
//                statement.executeUpdate(sql);
//                System.out.println(sql);
//            }
//            System.out.println("=======================插入专业结束========================");
            //========================插入课程数据================================================
//            System.out.println("=======================插入课程开始========================");
//            for (String sql : getAddCourseSql()) {
//                statement.executeUpdate(sql);
//                System.out.println(sql);
//            }
//            System.out.println("=======================插入课程结束========================");
            //==========================插入学生数据=============================================
//            String sqlM = "SELECT  * FROM `major`";
//                ResultSet resultSetS2 = statement.executeQuery(sqlM);
//                ArrayList<HashMap<String, String>> majors = new ArrayList<>();
//                while (resultSetS2.next()) {
//                    HashMap map = new HashMap();
//                    map.put("id", resultSetS2.getObject("id") + "");
//                    map.put("mno", resultSetS2.getObject("mno") + "");
//                    majors.add(map);
//                }
//                System.out.println("=======================插入学生开始========================");
//                PreparedStatement preparedStatement = conn.prepareStatement("INSERT IGNORE INTO student (id,sno,sname,major_id,sex,`status`,version,create_time) VALUES(?,?,?,?,?,?,?,?);");
//                conn.setAutoCommit(false);
//                for (HashMap<String,String> data : getAddStudentSql(majors)) {
//                    preparedStatement.setObject(1,data.get("id"));
//                    preparedStatement.setObject(2,data.get("sno"));
//                    preparedStatement.setObject(3,data.get("sname"));
//                    preparedStatement.setObject(4,data.get("major_id"));
//                    preparedStatement.setObject(5,data.get("sex"));
//                    preparedStatement.setObject(6,data.get("status"));
//                    preparedStatement.setObject(7,data.get("version"));
//                    preparedStatement.setObject(8,data.get("create_time"));
//                    preparedStatement.addBatch(); // 添加到批处理
//                    System.out.println("学生信息已添加到批处理>>");
//                }
//                System.out.println("开始执行批量插入...");
//                System.out.println("时间::"+getSystemDateTime());
//                int[] updateCounts = preparedStatement.executeBatch();
//                conn.commit();
//                resultSetS2.close();
//                System.out.println("批量新增成功！");
//                System.out.println("执行完成>>" + updateCounts);
//                System.out.println("时间::" + getSystemDateTime());
//                System.out.println("=======================插入学生结束========================");


            //=============================选课信息===========================================
//            //执行语句
//            String sqlS = "SELECT  * FROM `student` LIMIT  500000,50000";
//////            返回结果
//            ResultSet resultSetS3 = statement.executeQuery(sqlS);
//            ArrayList<HashMap<String, String>> stus = new ArrayList<>();
//            while (resultSetS3.next()) {
//                HashMap map = new HashMap();
//                map.put("id", resultSetS3.getObject("id"));
//                map.put("sno", resultSetS3.getObject("sno"));
//                stus.add(map);
//            }
//            String sqlC = "SELECT  * FROM `course`";
//            ResultSet resultSetC = statement.executeQuery(sqlC);
//            ArrayList<HashMap<String, Long>> courses = new ArrayList<>();
//            while (resultSetC.next()) {
//                HashMap map = new HashMap();
//                map.put("id", resultSetC.getObject("id"));
//                map.put("cno", resultSetC.getObject("cno"));
//                courses.add(map);
//            }
//            System.out.println("=======================插入选课开始========================");
//            //插入选课信息
//            ArrayList<HashMap<String, String>> a = getAddStuCourseSql(stus, courses);
//            PreparedStatement preparedStatement2 = conn.prepareStatement("INSERT IGNORE INTO stu_course (id,stu_id,course_id,`session`,`status`,version,create_time) VALUES(?,?,?,?,?,?,?);");
//            conn.setAutoCommit(false);
//            for (HashMap<String, String> data : a) {
//                preparedStatement2.setObject(1, data.get("id"));
//                preparedStatement2.setObject(2, data.get("stu_id"));
//                preparedStatement2.setObject(3, data.get("course_id"));
//                preparedStatement2.setObject(4, data.get("session"));
//                preparedStatement2.setObject(5, data.get("status"));
//                preparedStatement2.setObject(6, data.get("version"));
//                preparedStatement2.setObject(7, data.get("create_time"));
//                preparedStatement2.addBatch(); // 添加到批处理
//                System.out.println("选课已添加到批处理>>");
//            }
//            System.out.println("开始执行批量插入>>");
//            System.out.println("时间::" + getSystemDateTime());
//            int[] updateCounts2 = preparedStatement2.executeBatch();
//            conn.commit();
//            System.out.println("批量新增成功>>" + updateCounts2);
//            System.out.println("时间::" + getSystemDateTime());
//            System.out.println("=======================插入选课结束========================");
////            释放资源
//            resultSetC.close();

            statement.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC驱动未找到");
            e.printStackTrace();
        } catch (SQLException e2) {
            System.out.println("数据库连接失败");
            e2.printStackTrace();
        }
    }

    public static void main(String[] args) {
        GenerateData generateData = new GenerateData();
        SnowFlake snowFlake = new SnowFlake(2, 3);
        System.out.println("初始化数据开始::" + generateData.getSystemDateTime());
        generateData.MySQLOperate();
        System.out.println("初始化数据结束::" + generateData.getSystemDateTime());
    }

}
